package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.ControleVersaoEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class ControleNavsLuaDao {
	public Connection connection;

	@SuppressWarnings("static-access")
	public ControleNavsLuaDao() {
		this.connection = new ConnectionFactory().getConnection();
	}
	
	public ControleNavsLuaDao(HttpSession session) {
		this.connection = utils.recoverSessionDBConnection(session);
	}

	public ControleNavsLuaDao(Connection con) {
		this.connection = con;
	}

	public Connection getConnection() {
		return connection;
	}

	protected void finalize() throws Throwable {
		if(connection != null)
			if(connection.isClosed() == true)
				connection.close();
		connection = null;
		super.finalize();
	}

	/*--------------------------------------------------------------------------------------------------------
	 * Insert
	 --------------------------------------------------------------------------------------------------------*/

	/*--------------------------------------------------------------------------------------------------------
	 * update
	 --------------------------------------------------------------------------------------------------------*/



	public ControleVersaoEntity update(ControleVersaoEntity contVersao) throws Exception {

		try {
			PreparedStatement stmt = connection.prepareStatement(this.getQryUpdate());

			stmt.setString(1, contVersao.getVersaoLua());
			stmt.setString(2, contVersao.getVersaoGateway());
			stmt.setString(3, contVersao.getVersaoNavs());
			stmt.setString(4, contVersao.getVersaoOs());
			stmt.setString(5, contVersao.getVersaoEos());

			stmt.execute();
			//connection.commit();
			stmt.close();

		} catch (SQLException e) {

			throw new Exception(e);
		} catch (Exception e) {
			throw new Exception(e);
		}

		return contVersao;

	}

	/*--------------------------------------------------------------------------------------------------------
	 * Select
	 --------------------------------------------------------------------------------------------------------*/
	public ControleVersaoEntity select() throws Exception {
		Connection cnn = new ConnectionFactory().getConnection();
		ControleVersaoEntity result = null;
		try {
			PreparedStatement stmt = cnn.prepareStatement(this.getQrySelect());
			result = this.execSelect(stmt);
			stmt.close();
			cnn.close();	
		} catch (Exception e) {
			cnn.close();
		}		
		return result;
	}

	private ControleVersaoEntity execSelect(PreparedStatement stmt) throws Exception {
		ControleVersaoEntity contVersao = null;
		try {
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				contVersao = new ControleVersaoEntity();

				contVersao.setId(rs.getInt("id"));
				contVersao.setVersaoLua(rs.getString("versao_lua"));
				contVersao.setVersaoGateway(rs.getString("versao_gateway"));
				contVersao.setVersaoNavs(rs.getString("versao_navs"));
				contVersao.setVersaoOs(rs.getString("versao_os"));
				contVersao.setVersaoEos(rs.getString("versao_eos"));

			}
			rs.close();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return contVersao;
	}

	/*--------------------------------------------------------------------------------------------------------
	 * Query
	 --------------------------------------------------------------------------------------------------------*/

	private String getQrySelect() {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT id, ");
		sql.append("       versao_lua, ");
		sql.append("       versao_gateway, ");
		sql.append("       versao_navs, ");
		sql.append("       versao_os, ");
		sql.append("       versao_eos ");
		sql.append("FROM   controle_versao ");
		sql.append("WHERE  id = 1; ");

		return sql.toString();

	}

	private String getQryInsert() {

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO controle_versao ");
		sql.append("            (id, ");
		sql.append("             versao_lua, ");
		sql.append("             versao_gateway, ");
		sql.append("             versao_navs, ");
		sql.append("             versao_os, ");
		sql.append("             versao_eos) ");
		sql.append("VALUES      (1, ?, ?, ?, ?, ?); ");

		return sql.toString();

	}

	private String getQryUpdate() {

		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE controle_versao ");
		sql.append("SET    versao_lua = ? , ");
		sql.append("       versao_gateway = ? , ");
		sql.append("       versao_navs = ? , ");
		sql.append("       versao_os = ? , ");
		sql.append("       versao_eos = ?  ");
		sql.append("WHERE  id = 1 ; ");

		return sql.toString();

	}


	private String getQryDelete() {

		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM controle_versao ");
		sql.append("WHERE  id = 1;  ");
		return sql.toString();

	}
}
